﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using iTextSharp;
using iTextSharp.text.pdf;




   static class SupermarketDAO
    {


        public static void SelectSales(PdfPTable table,ref decimal totalSum)
        {
            string MyConString = "SERVER=localhost;" + "DATABASE=supermarket;" + "UID=root;" + "PASSWORD=rootpass;";
            using (MySqlConnection connection = new MySqlConnection(MyConString))
            {
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;
                command.CommandText = "SELECT p.Name as 'Product', s.Quantity, m.Name as 'Measure', p.Price, s.Location, (s.Quantity * p.Price) as 'Sum' " +
                    "FROM Sales s JOIN Products p ON s.ProductID=p.ID "+
                                    "JOIN Vendors v ON p.VendorID=v.ID " +
                                    "JOIN Measures m ON p.MeasureID=m.ID";
                connection.Open();
                Reader = command.ExecuteReader();
                while (Reader.Read())
                {
             
                    table.AddCell(Reader.GetValue(0).ToString());
                    table.AddCell(Reader.GetValue(1).ToString() + " " + Reader.GetValue(2).ToString());
                    table.AddCell(Reader.GetValue(3).ToString());
                    table.AddCell(Reader.GetValue(4).ToString());
                    table.AddCell(Reader.GetValue(5).ToString());
                    totalSum += Decimal.Parse(Reader.GetValue(5).ToString());
                }
            }
        }

        public static string GetProductById(int id)
        {
            string MyConString = "SERVER=localhost;" + "DATABASE=supermarket;" + "UID=root;" + "PASSWORD=rootpass;";
            using (MySqlConnection connection = new MySqlConnection(MyConString))
            {
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;
                command.CommandText = "SELECT p.Name " +
                    "FROM Products p " +
                    "WHERE p.ID=" + id;
                connection.Open();
                Reader = command.ExecuteReader();

                Reader.Read();
                return Reader.GetValue(0).ToString();

            }
        }
        public static string GetMeasureById(int id)
        {
            string MyConString = "SERVER=localhost;" + "DATABASE=supermarket;" + "UID=root;" + "PASSWORD=rootpass;";
            using (MySqlConnection connection = new MySqlConnection(MyConString))
            {
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;
                command.CommandText = "SELECT m.Name " +
                    "FROM Products p JOIN Measures m ON p.MeasureID=m.ID " +
                    "WHERE p.ID=" + id;
                connection.Open();
                Reader = command.ExecuteReader();

                Reader.Read();
                return Reader.GetValue(0).ToString();

            }
        }
    }

